﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QLLopHoc.DoiTuongDuLieu;
using System.Data;

namespace QLLopHoc.KetNoiDatabase
{
    public class KNSinhVien
    {
        public static DTSinhVien LaySVTuMS(string mssv)
        {
            DTSinhVien kq = null;
            string sql = "select * from SinhVien where mssv='" + mssv + "'";
            DataTable dt = DataAccess.execquery(sql);
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                kq = new DTSinhVien();
                kq.MSSV = (string)dr["MSSV"];
                kq.Hoten = (string)dr["HoTen"];
                kq.Email = (string)dr["Email"];
                kq.Tel = (string)dr["Tel"];
            }
            return kq;
        }
        public static bool ThemSV(DTSinhVien sv)
        {
            string sql = "INSERT INTO SinhVien(MSSV,HoTen,Email,Tel) VALUES('" + sv.MSSV + "','" + sv.Hoten + "','" + sv.Email + "','" + sv.Tel + "')";
            //string sql = "INSERT INTO SinhVien(MSSV,HoTen,Email,Tel) VALUES('" + sv.MSSV + "','" + sv.HoTen + "','" + sv.Email + "','" + sv.Tel + "')";
            if (DataAccess.ExecNonQuery(sql) > 0)
            {
                return true;
            }
            return false;
        }
        //Phuong thuc xoa mot sinh vien
        public static bool XoaSV(string mssv)
        {
            string sql = "DELETE from SinhVien where MSSV='" + mssv + "'";
            if (DataAccess.ExecNonQuery(sql) > 0)
            {
                return true;
            }
            return false;
        }
        public static bool CapNhatSinhVien(DTSinhVien sv)
        {
            string sql = "update SinhVien set HoTen='" + sv.Hoten + "',Email='" + sv.Email + "',Tel='" + sv.Tel + "',where MSSV='" + sv.MSSV + "'";
            if (DataAccess.ExecNonQuery(sql) > 0)
            {
                return true;
            }
            return false;
        }

        //phuong thuc lay danh sach sinh vien
        public static List<DTSinhVien> LayDSSV() 
        { List<DTSinhVien> ds = new List<DTSinhVien>(); 
          string sql = "SELECT * FROM SinhVien"; 
          DataTable dt = DataAccess.execquery(sql); 
          DTSinhVien sv = null; 
          foreach (DataRow dr in dt.Rows) 
              { 
              sv = new DTSinhVien(); 
              sv.MSSV = (string)dr["MSSV"]; 
              sv.Hoten = (string)dr["HoTen"]; 
              sv.Email = (string)dr["Email"]; 
              sv.Tel = (string)dr["Tel"]; 
              ds.Add(sv); 
              } 
            return ds; 
        }
    }
}
